library(readxl)
library(tidyverse)

l1 <- list.files("/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Gallup and YouGov/YouGov Trackers/For analysis", full.names = TRUE)

topic <- c("Crime local",
           "Crime national",
           "Prisons effective at reducing crime",
           "Confidence in police",
           "Walk streets",
           "Sentencing level",
           "Death penalty multiple murder", 
           "Death penalty terrorist murder",
           "Death penalty child murder",
           "Death penalty police murder",
           "Death penalty all murder",
           "Sentencing purpose")

sheets <- c("All adults","Male","Female","18-24","25-49","50-64","65+")

for (x in 1:12){
  for (y in 1:7){
    url <- paste0("/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Gallup and YouGov/YouGov Trackers/Finished/",topic[x],'_',sheets[y],".csv")
    read_excel(l1[[x]], sheet = sheets[y]) %>%
      pivot_longer(cols = -c(1)) %>%
      pivot_wider(names_from = c(1), values_from = value) %>%
      mutate(Year = substr(name,1,4)) %>%
      select(!(c(name, `Unweighted base`, Base))) %>%
      group_by(Year) %>%
      summarise(across(everything(),mean, na.rm = TRUE)) %>%
      full_join(., read_excel(l1[[x]], sheet = sheets[y]) %>%
                  pivot_longer(cols = -c(1)) %>%
                  pivot_wider(names_from = c(1), values_from = value) %>%
                  mutate(Year = substr(name,1,4)) %>%
                  select((c(Year, `Unweighted base`))) %>%
                  group_by(Year) %>%
                  summarise(n = sum(`Unweighted base`, na.rm = TRUE))) %>%
      write.csv(., url)
  }
}

